import pandas as pd
from cantools.database.can import Message,Signal,Database,Node
import cantools
import numpy as np

file_Path="D:\\工作\\150HV甲醇车开发\\通讯协议\\徐工XGH150整车CAN网络通信协议V12.xlsx"


Can_MessageName=[]
Can_MessageID=[]
Can_MessageCycleTime=[]
Can_MessageLength=[]
Can_SignalName=[]
Can_SignalDescription=[]
Can_StartByte=[]
Can_StartBit=[]
Can_SignalBitLength=[]
Can_SignalOffset=[]
Can_Signalfactor=[]
Can_SignalMinValue=[]
Can_SignalMaxValue=[]
Can_SignalUnit=[]

#读取EXCEL文件
def OpenExcelFile():
    global sheet_names
    global column_headers
    global Can_MessageName
    global Can_MessageID
    global Can_MessageCycleTime
    global Can_MessageLength
    global Can_SignalName
    global Can_SignalDescription
    global Can_StartByte
    global Can_StartBit
    global Can_SignalBitLength
    global Can_SignalOffset
    global Can_Signalfactor
    global Can_SignalMinValue
    global Can_SignalMaxValue
    global Can_SignalUnit
    Mesagename=[]
    Signalname=[]


    with pd.ExcelFile(file_Path) as excel:
        sheet_names=excel.sheet_names  #获取所有sheet名称
        df_header = pd.read_excel(excel, sheet_name=sheet_names[6], nrows=0)
        column_headers = df_header.columns.tolist()
        df=pd.read_excel(file_Path,sheet_names[6])
    print("Sheetname\n",sheet_names)
    print(sheet_names[4])
    print("列标题\n",column_headers)

    #读取报文名称
    Can_MessageName = df[column_headers[0]].tolist()
    #读取报文ID
    Can_MessageID=df[column_headers[2]].tolist()
    #读取报文周期
    Can_MessageCycleTime=df[column_headers[4]].tolist()
    #读取报文长度
    Can_MessageLength=df[column_headers[5]].tolist()
    #读取信号名称
    Can_SignalName=df[column_headers[6]].tolist()
    #读取信号描述
    Can_SignalDescription=df[column_headers[7]].tolist()
    #读取信号起始字节
    Can_StartBit=df[column_headers[9]].tolist()
    #读取信号起始位
    Can_StartBit=df[column_headers[10]].tolist()
    #读取信号长度
    Can_SignalBitLength=df[column_headers[11]].tolist()
    #读取信号精度
    Can_Signalfactor=df[column_headers[13]].tolist()
    #读取信号偏移量
    Can_SignalOffset=df[column_headers[14]].tolist()

    Can_SignalMinValue=df[column_headers[15]].tolist()
    Can_SignalMaxValue=df[column_headers[16]].tolist()

    #Can_MessageName = [str(x) if not (pd.isna(x) or x is None) else "000" for x in Can_MessageName]
    #将NAN转为000标识符


    #处理名称中的空格替换为“_”
    old_Can_MessageName=Can_MessageName
    old_Can_SignalName=Can_SignalName

    for name in old_Can_MessageName:
        if(name!="nan"):
            name=str(name).replace(" ","_")
            Mesagename.append(name)
    for name in old_Can_SignalName:
        if(name!="nan"):
            name=str(name).replace(" ","_")
            Signalname.append(name)




    # for num in range(0, len(Can_SignalMinValue)):
    #     if (Can_SignalMinValue[num] == "nan"):
    #         Can_SignalMinValue[num] = 1
    # for num in range(0, len(Can_SignalMaxValue)):
    #     if (Can_SignalMaxValue[num] == "nan"):
    #         Can_SignalMaxValue[num] = 255

    Can_SignalName=Signalname
    Can_MessageName = Mesagename


    Can_MessageID = [str(x) if not (pd.isna(x) or x is None) else "000" for x in Can_MessageID]
    Can_SignalMinValue = [str(x) if not (pd.isna(x) or x is None) else "0" for x in Can_SignalMinValue]
    Can_SignalMaxValue = [str(x) if not (pd.isna(x) or x is None) else "255" for x in Can_SignalMaxValue]

    for num in range(len(Can_MessageID)):
        if(Can_MessageID[num]!="000"):
            Can_SignalName[num]="nan"

    print("value\n",Can_SignalMinValue)
    print(len(Can_SignalMinValue))
    #Can_SignalName = [str(x) if not (pd.isna(x) or x is None) else "000" for x in Can_SignalName]
    print(Can_SignalName)
    print(Can_MessageID)
#创建信号列表
def CreateSignalLIST():
    signalList=[]#信号列表
    SignalLIST=[]
    for num in range(1,len(Can_SignalName)):
        if(Can_SignalName[num]=="nan"):
            SignalLIST.append(signalList)
            signalList=[]
        else:
            print(Can_MessageID[num],Can_StartBit[num],Can_SignalName[num])

            siganl1 = Signal(
                name=Can_SignalName[num],
                start=int(Can_StartBit[num]),
                length=int(Can_SignalBitLength[num]),
                byte_order="little_endian",
                minimum=float(Can_SignalMinValue[num]),
                maximum=float(Can_SignalMaxValue[num]),
                comment=str(Can_SignalDescription[num]),

                # raw_initial=0.1,#初始DBC
                is_signed=False,
            )
            siganl1.offset = float(Can_SignalOffset[num])
            siganl1.scale = float(Can_Signalfactor[num])
            signalList.append(siganl1)
    SignalLIST.append(signalList)
    return SignalLIST

def CreateMessageLIST():
    SignalList=CreateSignalLIST()
    print(SignalList)
    MessageList=[]
    flag=0
    for num in range(0,len(Can_MessageID)):
        if(Can_MessageID[num]!="000"):
            message1=Message(
                frame_id=int(Can_MessageID[num],16),
                name=Can_MessageName[num],
                length=8,
                signals=SignalList[flag],
                bus_name="PFC",
                header_byte_order='little_endian',
                is_extended_frame=True,
                send_type='cyclic',
                senders=['PFC'],
                cycle_time=20,
                comment='input voltage',
                unused_bit_pattern=255,
            )
            MessageList.append(message1)
            flag=flag+1
    return MessageList

def main():
    OpenExcelFile()
    messagelist=CreateMessageLIST()
    print(messagelist)
    node1 = Node(
        name='PFC',
        comment="test"
    )
    database = Database(
        messages=messagelist,
        nodes=[node1],
        version='1.0'
    )
    cantools.database.dump_file(database, 'example2.dbc')
    #column_data = df[column_headers[6]]
   # print(f"列 '{column_headers[6]}' 的数据:\n", column_data.tolist())
   # print(len(column_data.tolist()))


if __name__ == '__main__':
    main()




